package studio7Omega.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import studio7Omega.excepcion.DAOExcepcion;
import studio7Omega.modelo.Instrumento;
import studio7Omega.modelo.Servicio;
import studio7Omega.util.ConexionBD;

public class SalaItemDAO extends BaseDAO {

    public static boolean insertarItem(int idsala, int iditem) throws DAOExcepcion {
        Connection con = null;
        PreparedStatement stmt = null;
        boolean rpta=false;
        try {
            con = ConexionBD.obtenerConexion();
            String query = "insert into salaitem(idsala,iditem) values(?,?)";
            stmt = con.prepareStatement(query);
            stmt.setInt(1, idsala);
            stmt.setInt(2, iditem);
            if(stmt.executeUpdate()>0){
                rpta= true;
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
            rpta= false;
            throw new DAOExcepcion(e.getMessage());
        } finally {
            try {
                stmt.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
                rpta=false;
            }
        }
        return rpta;
    }

    public static boolean eliminarItem(int idsala, int iditem) throws DAOExcepcion {
        Connection con = null;
        PreparedStatement stmt = null;
        boolean rpta=false;
        try {
            con = ConexionBD.obtenerConexion();
            String query = "delete from salaitem where idsala = ? and iditem = ?";
            stmt = con.prepareStatement(query);
            stmt.setInt(1, idsala);
            stmt.setInt(2, iditem);
            if(stmt.executeUpdate()>0){
                rpta= true;
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
            rpta= false;
            throw new DAOExcepcion(e.getMessage());
        } finally {
            try {
                stmt.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
                rpta=false;
            }
        }
        return rpta;
    }

    public static Collection<Instrumento> obtenerInstrumentos(int idsala) throws DAOExcepcion {
        Collection<Instrumento> c = new ArrayList<Instrumento>();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConexionBD.obtenerConexion();
            String query = "select i.iditem, i.descripcion, i.tipo, i.costo, i.tipoIns, i.marca, i.modelo "
                    + "from salaitem s inner join item i  on  i.iditem = s.iditem "
                    + " where s.idsala = ? and i.tipo = 'instrumento'";
            stmt = con.prepareStatement(query);
            stmt.setInt(1, idsala);
            rs = stmt.executeQuery();
            Instrumento instrumento = null;
            while (rs.next()) {
                instrumento = new Instrumento();
                instrumento.setIdItem(rs.getInt("iditem"));
                instrumento.setTipo(rs.getString("tipo"));
                instrumento.setDescripcion(rs.getString("descripcion"));
                instrumento.setTipoIns(rs.getString("tipoIns"));
                instrumento.setCosto(rs.getDouble("costo"));
                instrumento.setMarca(rs.getString("marca"));
                instrumento.setModelo(rs.getString("modelo"));
                c.add(instrumento);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
        return c;
    }

     public static Collection<Servicio> obtenerServicios(int idsala) throws DAOExcepcion {
        Collection<Servicio> c = new ArrayList<Servicio>();
        Connection con = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            con = ConexionBD.obtenerConexion();
            String query = "select i.iditem, i.descripcion, i.tipo, i.costo, i.tipoIns, i.marca, i.modelo "
                    + "from salaitem s inner join item i  on  i.iditem = s.iditem "
                    + " where s.idsala = ? and i.tipo = 'servicio'";
            stmt = con.prepareStatement(query);
            stmt.setInt(1, idsala);
            rs = stmt.executeQuery();
            Servicio servicio;
            while (rs.next()) {
                servicio = new Servicio();
                servicio.setIdItem(rs.getInt("iditem"));
                servicio.setTipo(rs.getString("tipo"));
                servicio.setDescripcion(rs.getString("descripcion"));
                servicio.setCosto(rs.getDouble("costo"));
                c.add(servicio);
            }
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new DAOExcepcion(e.getMessage());
        } finally {
            try {
                rs.close();
                stmt.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println(ex.getMessage());
            }
        }
        return c;
    }
}
